Amazon Linux2 から pg_dump と pg_restore で RDS for PostgreSQL のバックアップ・リストアを検証してみた

こんにちは！コンサル部のinomaso(@inomasosan)です。

RDS for PostgreSQL内の特定データベースやテーブルを、pg_dumpとpg_restoreを使用してバックアップ・リストアできるか検証してみました。

まずは結論

RDS for PostgreSQL内に作成したデータベースやテーブルは、pg_dumpとpg_restoreを使用してバックアップ・リストア可能です。

しかし本番環境等で運用する場合、バックアップ運用やリストア時の推奨されるパラメータ変更で考慮が必要となりますので運用負荷が増えてしまいます。
そのためユースケースとしては、オンプレやEC2からRDSへのデータ移行や検証時の一部データベース・テーブルのバックアップ・リストア等が望ましいです。

基本的にはRDS標準機能であるDBスナップショットにてバックアップ運用して頂くほうが、運用負荷も大幅に軽減されますので良いかと思います。

やってみた

検証環境

項目	バージョン
Amazon Linux2	amzn2-ami-kernel-5.10-hvm-2.0.20211201.0-x86_64-gp2
pg_dump	13.5
PostgreSQL	13.4

構成

EC2(Amazon Linux2)とRDS for PostgreSQLが既に構築済みの前提となります。
検証ではRDS for PostgreSQLにdevelopmentというデータベースを追加で作成します。
今回は上記データベースと、その中のテーブルをバックアップ・リストアできるか検証していきます。

pg_dumpやpg_restoreを使用するために、今回の構成ではEC2(Amazon Linux2)にPostgreSQLのクライアントツールが必要となります。
EC2(Amazon Linux2)へのインストール方法については、下記ブログにまとめましたのでご参照ください。

尚、RDSのPostgreSQL拡張機能の追加は、今回の検証では特に必要ありませんでした。

検証用データベース・テーブル作成

EC2(Amazon Linux2)からRDS for PostgreSQLに接続し、検証用データベース・テーブルを作成していきます。

データベース接続

RDS for PostgreSQL作成時にデフォルトで作成されるpostgresデータベースに接続します。

$ psql -h <RDSエンドポイント> -U postgres -d postgres

データベース作成

developmentというデータベースを新規作成します。

postgres=> CREATE DATABASE development LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;

DB一覧の確認

developmentデータベースが正常に作成できたか確認します。

postgres=> \l
                                          データベース一覧
    名前     |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限

-------------+----------+------------------+-------------+-------------------+----------------------
-
 development | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 postgres    | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       |
 rdsadmin    | rdsadmin | UTF8             | en_US.UTF-8 | en_US.UTF-8       | rdsadmin=CTc/rdsadmin
+
             |          |                  |             |                   | rdstopmgr=Tc/rdsadmin
 template0   | rdsadmin | UTF8             | en_US.UTF-8 | en_US.UTF-8       | =c/rdsadmin
+
             |          |                  |             |                   | rdsadmin=CTc/rdsadmin
 template1   | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       | =c/postgres
+
             |          |                  |             |                   | postgres=CTc/postgres
(5 行)

データベース切断

postgresデータベースとの接続を切断します。

postgres=> \q

新規作成したデータベース接続

先ほど作成したdevelopmentデータベースに接続します。

$ psql -h <RDSエンドポイント> -U postgres -d development

テーブル作成

検証用にdevelopment_table1とdevelopment_table2の2つのテーブルを新規作成します。

development=>　CREATE TABLE development_table1 
(id char(4) not null,
name text not null,
PRIMARY KEY(id));

development=>　CREATE TABLE development_table2 
(id char(4) not null,
name text not null,
PRIMARY KEY(id));

テーブル一覧確認

作成したテーブルを確認します。

development=> \d
                  リレーション一覧
 スキーマ |        名前        |  タイプ  |  所有者
----------+--------------------+----------+----------
 public   | development_table1 | テーブル | postgres
 public   | development_table2 | テーブル | postgres
(2 行)

レコード追加

検証用に各テーブルにレコードを登録します。

development=> INSERT INTO development_table1(id,name) VALUES (1,'hogehoge1');
INSERT 0 1
development=> INSERT INTO development_table2(id,name) VALUES (1,'hogehoge2');
INSERT 0 1

テーブルのレコード確認

テーブルに登録したレコードを確認します。

development=> select * from development_table1;
  id  |   name
------+-----------
 1    | hogehoge1
(1 行)

development=> select * from development_table2;
  id  |   name
------+-----------
 1    | hogehoge2
(1 行)

データベース切断

developmentデータベースとの接続を切断します。

development=> \q

データベース単位のリストア

EC2(Amazon Linux2)からpg_dumpとpg_restoreを実行し、developmentデータベースのバックアップ・リストアを検証します。

バックアップ

pg_dumpでdevelopmentデータベースのバックアップを作成します。
-Fcオプションを指定する事で、バックアップはデフォルトで圧縮されます。

$ pg_dump -Fc -v -h <RDSエンドポイント> -U postgres development > db.dump
パスワード:
pg_dump: 最後の組み込みOIDは16383
pg_dump: 機能拡張を読み込んでいます
pg_dump: 機能拡張の構成要素を特定しています
pg_dump: スキーマを読み込んでいます
pg_dump: ユーザ定義テーブルを読み込んでいます
pg_dump: ユーザ定義関数を読み込んでいます
pg_dump: ユーザ定義型を読み込んでいます
pg_dump: 手続き言語を読み込んでいます
pg_dump: ユーザ定義集約関数を読み込んでいます
pg_dump: ユーザ定義演算子を読み込んでいます
pg_dump: ユーザ定義アクセスメソッドを読み込んでいます
pg_dump: ユーザ定義演算子クラスを読み込んでいます
pg_dump: ユーザ定義演算子族を読み込んでいます
pg_dump: ユーザ定義のテキスト検索パーサを読み込んでいます
pg_dump: ユーザ定義のテキスト検索テンプレートを読み込んでいます
pg_dump: ユーザ定義のテキスト検索辞書を読み込んでいます
pg_dump: ユーザ定義のテキスト検索設定を読み込んでいます
pg_dump: ユーザ定義の外部データラッパーを読み込んでいます
pg_dump: ユーザ定義の外部サーバーを読み込んでいます
pg_dump: デフォルト権限設定を読み込んでいます
pg_dump: ユーザ定義の照合順序を読み込んでいます
pg_dump: ユーザ定義の変換を読み込んでいます
pg_dump: 型キャストを読み込んでいます
pg_dump: 変換を読み込んでいます
pg_dump: テーブル継承情報を読み込んでいます
pg_dump: イベントトリガを読み込んでいます
pg_dump: 機能拡張構成テーブルを探しています
pg_dump: 継承関係を検索しています
pg_dump: 対象テーブルの列情報を読み込んでいます
pg_dump: テーブル"public.development_table1"の列と型を探しています
pg_dump: テーブル"public.development_table2"の列と型を探しています
pg_dump: 子テーブルの継承列にフラグを設定しています
pg_dump: インデックスを読み込んでいます
pg_dump: テーブル"public.development_table1"のインデックスを読み込んでいます
pg_dump: テーブル"public.development_table2"のインデックスを読み込んでいます
pg_dump: パーティション親テーブルのインデックスにフラグを設定しています
pg_dump: 拡張統計情報を読み込んでいます
pg_dump: 制約を読み込んでいます
pg_dump: トリガを読み込んでいます
pg_dump: 書き換えルールを読み込んでいます
pg_dump: ポリシを読み込んでいます
pg_dump: reading row-level security policies
pg_dump: パブリケーションを読み込んでいます
pg_dump: パブリケーションの構成要素を読み込んでいます
pg_dump: サブスクリプションを読み込んでいます
pg_dump: ラージオブジェクトを読み込んでいます
pg_dump: データの依存データを読み込んでいます
pg_dump: encoding = UTF8 を保存しています
pg_dump: standard_conforming_strings = on を保存しています
pg_dump: search_path =  を保存しています
pg_dump: データベース定義を保存しています
pg_dump: テーブル "public.development_table1"の内容をダンプしています
pg_dump: テーブル "public.development_table2"の内容をダンプしています

リストア

pg_restoreでdevelopmentデータベースをリストアします。
既存データベースへリストアするため、-cオプションで新しいテーブルが作成される前に古いテーブルが削除します。

$ pg_restore -c -v -h <RDSエンドポイント> -U postgres -d development db.dump
pg_restore: リストアのためデータベースに接続しています
パスワード:
pg_restore: CONSTRAINT development_table2 development_table2_pkeyを削除しています
pg_restore: CONSTRAINT development_table1 development_table1_pkeyを削除しています
pg_restore: TABLE development_table2を削除しています
pg_restore: TABLE development_table1を削除しています
pg_restore: TABLE "public.development_table1"を作成しています
pg_restore: TABLE "public.development_table2"を作成しています
pg_restore: テーブル"public.development_table1"のデータを処理しています
pg_restore: テーブル"public.development_table2"のデータを処理しています
pg_restore: CONSTRAINT "public.development_table1 development_table1_pkey"を作成しています
pg_restore: CONSTRAINT "public.development_table2 development_table2_pkey"を作成しています

-cオプションを指定せずに既存のデータベースへリストアしようとすると、テーブル作成時に重複エラーとなります。

$ pg_restore -v -h <RDSエンドポイント> -U postgres -d development db.dump
pg_restore: リストアのためデータベースに接続しています
パスワード:
pg_restore: TABLE "public.development_table1"を作成しています
pg_restore: TOC処理中:
pg_restore: TOCエントリ200; 1259 40978 TABLE development_table1 postgres から
pg_restore: エラー: could not execute query: ERROR:  relation "development_table1" already exists
コマンド: CREATE TABLE public.development_table1 (
    id character(4) NOT NULL,
    name text NOT NULL
);


pg_restore: TABLE "public.development_table2"を作成しています
pg_restore: TOCエントリ201; 1259 40986 TABLE development_table2 postgres から
pg_restore: エラー: could not execute query: ERROR:  relation "development_table2" already exists
コマンド: CREATE TABLE public.development_table2 (
    id character(4) NOT NULL,
    name text NOT NULL
);


pg_restore: テーブル"public.development_table1"のデータを処理しています
pg_restore: TOCエントリ3847; 0 40978 TABLE DATA development_table1 postgres から
pg_restore: エラー: テーブル"development_table1"へのコピーに失敗しました: ERROR:  duplicate key value violates unique constraint "development_table1_pkey"
DETAIL:  Key (id)=(1   ) already exists.
CONTEXT:  COPY development_table1, line 1
pg_restore: テーブル"public.development_table2"のデータを処理しています
pg_restore: TOCエントリ3848; 0 40986 TABLE DATA development_table2 postgres から
pg_restore: エラー: テーブル"development_table2"へのコピーに失敗しました: ERROR:  duplicate key value violates unique constraint "development_table2_pkey"
DETAIL:  Key (id)=(1   ) already exists.
CONTEXT:  COPY development_table2, line 1
pg_restore: CONSTRAINT "public.development_table1 development_table1_pkey"を作成しています
pg_restore: TOCエントリ3714; 2606 40985 CONSTRAINT development_table1 development_table1_pkey postgres から
pg_restore: エラー: could not execute query: ERROR:  multiple primary keys for table "development_table1" are not allowed
コマンド: ALTER TABLE ONLY public.development_table1
    ADD CONSTRAINT development_table1_pkey PRIMARY KEY (id);


pg_restore: CONSTRAINT "public.development_table2 development_table2_pkey"を作成しています
pg_restore: TOCエントリ3716; 2606 40993 CONSTRAINT development_table2 development_table2_pkey postgres から
pg_restore: エラー: could not execute query: ERROR:  multiple primary keys for table "development_table2" are not allowed
コマンド: ALTER TABLE ONLY public.development_table2
    ADD CONSTRAINT development_table2_pkey PRIMARY KEY (id);


pg_restore: 警告: リストア中に無視されたエラー数: 6

テーブル単位のリストア

EC2(Amazon Linux2)からpg_dumpとpg_restoreを実行し、developmentデータベース内の特定テーブルのバックアップ・リストアを検証します。

バックアップ

pg_dumpでdevelopmentデータベースのバックアップを作成します。
-Fcオプションを指定する事で、バックアップはデフォルトで圧縮されます。
-tオプションでdevelopment_table1テーブルのみをバックアップ対象としています。

$ pg_dump -Fc -v -h <RDSエンドポイント> -U postgres -t development_table1 development > db_table.dump
パスワード:
pg_dump: 最後の組み込みOIDは16383
pg_dump: 機能拡張を読み込んでいます
pg_dump: 機能拡張の構成要素を特定しています
pg_dump: スキーマを読み込んでいます
pg_dump: ユーザ定義テーブルを読み込んでいます
pg_dump: ユーザ定義関数を読み込んでいます
pg_dump: ユーザ定義型を読み込んでいます
pg_dump: 手続き言語を読み込んでいます
pg_dump: ユーザ定義集約関数を読み込んでいます
pg_dump: ユーザ定義演算子を読み込んでいます
pg_dump: ユーザ定義アクセスメソッドを読み込んでいます
pg_dump: ユーザ定義演算子クラスを読み込んでいます
pg_dump: ユーザ定義演算子族を読み込んでいます
pg_dump: ユーザ定義のテキスト検索パーサを読み込んでいます
pg_dump: ユーザ定義のテキスト検索テンプレートを読み込んでいます
pg_dump: ユーザ定義のテキスト検索辞書を読み込んでいます
pg_dump: ユーザ定義のテキスト検索設定を読み込んでいます
pg_dump: ユーザ定義の外部データラッパーを読み込んでいます
pg_dump: ユーザ定義の外部サーバーを読み込んでいます
pg_dump: デフォルト権限設定を読み込んでいます
pg_dump: ユーザ定義の照合順序を読み込んでいます
pg_dump: ユーザ定義の変換を読み込んでいます
pg_dump: 型キャストを読み込んでいます
pg_dump: 変換を読み込んでいます
pg_dump: テーブル継承情報を読み込んでいます
pg_dump: イベントトリガを読み込んでいます
pg_dump: 機能拡張構成テーブルを探しています
pg_dump: 継承関係を検索しています
pg_dump: 対象テーブルの列情報を読み込んでいます
pg_dump: テーブル"public.development_table1"の列と型を探しています
pg_dump: 子テーブルの継承列にフラグを設定しています
pg_dump: インデックスを読み込んでいます
pg_dump: テーブル"public.development_table1"のインデックスを読み込んでいます
pg_dump: パーティション親テーブルのインデックスにフラグを設定しています
pg_dump: 拡張統計情報を読み込んでいます
pg_dump: 制約を読み込んでいます
pg_dump: トリガを読み込んでいます
pg_dump: 書き換えルールを読み込んでいます
pg_dump: ポリシを読み込んでいます
pg_dump: reading row-level security policies
pg_dump: パブリケーションを読み込んでいます
pg_dump: パブリケーションの構成要素を読み込んでいます
pg_dump: サブスクリプションを読み込んでいます
pg_dump: データの依存データを読み込んでいます
pg_dump: encoding = UTF8 を保存しています
pg_dump: standard_conforming_strings = on を保存しています
pg_dump: search_path =  を保存しています
pg_dump: データベース定義を保存しています
pg_dump: テーブル "public.development_table1"の内容をダンプしています

リストア

pg_restoreでdevelopmentデータベースをリストアします。
既存データベースへリストアするため、-cオプションで新しいテーブルが作成される前に古いテーブルが削除します。
pg_dumpでdevelopment_table1テーブルのみバックアップ対象としているので、リストアもdevelopment_table1テーブルのみとなります。

$ pg_restore -c -v -h <RDSエンドポイント> -U postgres -d development db_table.dump
pg_restore: リストアのためデータベースに接続しています
パスワード:
pg_restore: CONSTRAINT development_table1 development_table1_pkeyを削除しています
pg_restore: TABLE development_table1を削除しています
pg_restore: TABLE "public.development_table1"を作成しています
pg_restore: テーブル"public.development_table1"のデータを処理しています
pg_restore: CONSTRAINT "public.development_table1 development_table1_pkey"を作成しています

参考URL

まとめ

RDS for PostgreSQLでpg_dumpやpg_restoreを利用できるかについてわからなかったため、実際に検証で試してみました。
移行や検証等のユースケースでは有用ですが、本番環境等ではRDSのDBスナップショットでのバックアップ運用を推奨いたします。

この記事が、どなたかのお役に立てば幸いです。それでは！